import pandas as pd

# 读取两个Excel文件
df1 = pd.read_excel('data2_results.xlsx')  # 表格1
df2 = pd.read_excel('data2(800ture_label).xlsx', sheet_name='Sheet1')  # 表格2

# 检查列是否存在
if df1.shape[1] >= 4 and df2.shape[1] >= 3:
    # 创建一个新列来存储比较结果
    df1['Comparison'] = ''

    # 遍历df1中的每一行进行比较
    for index, row in df1.iterrows():
        if row.iloc[3] == df2.iat[index, 3]:
            df1.at[index, 'Comparison'] = '正确'
        elif row.iloc[3] == 0 and df2.iat[index, 3] == 1:
            df1.at[index, 'Comparison'] = '假阴性'
        elif row.iloc[3] == 1 and df2.iat[index, 3] == 0:
            df1.at[index, 'Comparison'] = '假阳性'

    # 计算统计数据
    total_count = df1['Comparison'].count()
    correct_count = (df1['Comparison'] == '正确').sum()
    false_negative_count = (df1['Comparison'] == '假阴性').sum()
    false_positive_count = (df1['Comparison'] == '假阳性').sum()
    negative_count = (df2['label'] == 0).sum()
    positive_count = (df2['label'] == 1).sum()
    # 计算正确率、假阳性率和假阴性率
    accuracy = correct_count / total_count*100
    false_positive_rate = false_positive_count / negative_count*100
    false_negative_rate = false_negative_count / positive_count*100

    # 打印统计结果
    print(negative_count)
    print(f"正确率: {accuracy:.4f}%")
    print(f"假阳性率: {false_positive_rate:.4f}%")
    print(f"假阴性率: {false_negative_rate:.4f}%")

    # 如果需要，可以将比较结果保存到新的Excel文件中
    df1.to_excel('comparison_results2.xlsx', index=False)
else:
    print("列索引不匹配或列不存在。")